/**
 * Auto gen via dbpojo-gen.
 */
package com.us.usws.dao;

import java.util.List;

import net.iwsh.core.db.DBExecutor;
import net.iwsh.core.db.ext.DialectSyntaxParser;
import net.iwsh.core.db.ext.SQLWithParams;
import net.iwsh.core.exception.DBException;
import net.iwsh.core.utils.IDGenerator;

import org.apache.commons.lang3.StringUtils;

import com.us.usws.model.domain.system.School;
import com.us.usws.model.domain.video.VideoFile;
import com.us.usws.model.flat.system.DOSchool;
import com.us.usws.model.flat.video.DOVideoFile;
import com.us.usws.support.uitls.DataPageUtil;

/**
 * DAO class
 * @author robot
 */
public class SchoolDAO extends BasePageSearchDAO<School>{

	/**
	 * Query SQLs
	 */
	private static final String SQL_GET_TOTAL_COUNT = "select count(*) from sys_school";

	private static final String SQL_GET_ALL_SCHOOLS = "select * from sys_school";

	private static final String SQL_GET_SCHOOL_BY_PK = "select * from sys_school where uid = ?";

	/**
	 * CUR SQLs
	 */
	private static final String SQL_INSERT_SCHOOL = "insert into sys_school(uid,code,name,school_type,province_code,city_code,strict_code,street,website_url,description,edu_stage, is_front_show,video_file_uid,is_valid,create_by_uid,create_date,last_upd_by_uid,last_upd_date) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_SCHOOL = "update sys_school set code=?, name=?, school_type=?, province_code=?, city_code=?, strict_code=?, street=?, website_url=?, description=?, edu_stage=?, is_front_show=?, video_file_uid=?, is_valid=?, last_upd_by_uid=?, last_upd_date=? where uid=?";

	private static final String SQL_UPDATE_SCHOOL_IS_VALID = "update sys_school set is_valid=? where uid=?";

	private static final String SQL_DEL_SCHOOL = "delete from sys_school where uid=?";
	
	private static final String SQL_INSERT_VIDEOFILE = "insert into hv_video_file(uid,orig_file_name,snapshot,file_size,mime_type,play_len,file_ext,create_by_uid,create_date,last_upd_by_uid,last_upd_date) values (?,?,?,?,?,?,?,?,?,?,?)";

	private static final String SQL_UPDATE_VIDEOFILE = "update hv_video_file set orig_file_name=?, snapshot=?, file_size=?, mime_type=?, play_len=?, file_ext=?, last_upd_by_uid=?, last_upd_date=? where uid=?";

	private static final String SQL_UPDATE_VIDEOFILE_SNAPSHOT = "update hv_video_file set snapshot=?, play_len=? where uid=?";

	private static final String SQL_UPDATE_VIDEO_FILE = "update hv_video_file set orig_file_name=?, file_size=?, mime_type=?, play_len=?, file_ext=?, last_upd_by_uid=?, last_upd_date=? where uid=?";
	/**
	 * DBExeutor
	 */
	private DBExecutor dbe = new DBExecutor();

	/**
	 * Get Count.
	 */
	public int getTotalCount() {
		return dbe.getCount(SQL_GET_TOTAL_COUNT);
	}

	/**
	 * Get all list.
	 */
	public List<School> getAllSchools() {
		return dbe.list(SQL_GET_ALL_SCHOOLS, School.class);
	}

	/**
	 * Get all list by pagination.
	 */
	public List<School> getSchools(int start, int end) {
		SQLWithParams swp = DialectSyntaxParser.getParser().getPaginationSQLWithParams(SQL_GET_ALL_SCHOOLS, null, start, end);
		return dbe.list(swp.getSql(), School.class, swp.getParams());
	}

	/**
	 * Get School by primary key.
	 */
	public School getSchool(String uid) {
		School s = dbe.load(SQL_GET_SCHOOL_BY_PK, School.class, uid);
		return s;
	}

	/**
	 * Insert one row.
	 */
	public String insertSchool(DOSchool school) {
		String uid = IDGenerator.getInstance().genUID();
		school.setUid(uid);
		dbe.execute(SQL_INSERT_SCHOOL, school.getUid(), school.getCode(), school.getName(), school.getSchoolType(),
				school.getProvinceCode(), school.getCityCode(), school.getStrictCode(), school.getStreet(), school.getWebsiteUrl(),
				school.getDescription(), school.getEduStage(), school.getIsFrontShow(), school.getVideoFileUid(), school.getIsValid(), school.getCreateByUid(),
				school.getCreateDate(), school.getLastUpdByUid(), school.getLastUpdDate());
		return uid;
	}
	
   public boolean insertSchool(DOSchool school, VideoFile videoFile) {
	   dbe.startTransaction();
		try {
			dbe.executeBatch(SQL_INSERT_VIDEOFILE, videoFile.getUid(), videoFile.getOrigFileName(), videoFile.getSnapshot(),
					videoFile.getFileSize(), videoFile.getMimeType(), videoFile.getPlayLen(), videoFile.getFileExt(),
					videoFile.getCreateByUid(), videoFile.getCreateDate(), videoFile.getLastUpdByUid(), videoFile.getLastUpdDate());

			dbe.executeBatch(SQL_INSERT_SCHOOL, school.getUid(), school.getCode(), school.getName(), school.getSchoolType(),
					school.getProvinceCode(), school.getCityCode(), school.getStrictCode(), school.getStreet(), school.getWebsiteUrl(),
					school.getDescription(), school.getEduStage(), school.getIsFrontShow(), school.getVideoFileUid(), school.getIsValid(), school.getCreateByUid(),
					school.getCreateDate(), school.getLastUpdByUid(), school.getLastUpdDate());
			return true;
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
   }
   
   /**
	 * Update by PK.
	 */
	public void updateSchool(DOSchool school, DOVideoFile videoFile) {
		dbe.startTransaction();
		try {
			if (StringUtils.isNotBlank(videoFile.getSnapshot()) && StringUtils.isBlank(videoFile.getOrigFileName())) {
				/*
				 * only update snapshot
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEOFILE_SNAPSHOT, videoFile.getSnapshot(), videoFile.getPlayLen(), videoFile.getUid());
			} else if (StringUtils.isNotBlank(videoFile.getOrigFileName()) && StringUtils.isBlank(videoFile.getSnapshot())) {
				/*
				 * only update video info 
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEO_FILE, videoFile.getOrigFileName(), videoFile.getFileSize(), videoFile.getMimeType(),
						videoFile.getPlayLen(), videoFile.getFileExt(), videoFile.getLastUpdByUid(), videoFile.getLastUpdDate(),
						videoFile.getUid());
			} else if (StringUtils.isNotBlank(videoFile.getSnapshot()) && StringUtils.isNotBlank(videoFile.getOrigFileName())) {
				/*
				 * update all info
				 */
				dbe.executeBatch(SQL_UPDATE_VIDEOFILE, videoFile.getOrigFileName(), videoFile.getSnapshot(), videoFile.getFileSize(),
						videoFile.getMimeType(), videoFile.getPlayLen(), videoFile.getFileExt(), videoFile.getLastUpdByUid(),
						videoFile.getLastUpdDate(), videoFile.getUid());
			} 
			dbe.executeBatch(SQL_UPDATE_SCHOOL, school.getCode(), school.getName(), school.getSchoolType(), school.getProvinceCode(),
					school.getCityCode(), school.getStrictCode(), school.getStreet(), school.getWebsiteUrl(), school.getDescription(),
					school.getEduStage(), school.getIsFrontShow(), school.getVideoFileUid(), school.getIsValid(), 
					school.getLastUpdByUid(), school.getLastUpdDate(), school.getUid());
		} catch (Exception e) {
			dbe.rollbackTransaction();
			e.printStackTrace();
			throw new DBException("excute failure!!");
		} finally {
			dbe.commitTransaction();
		}
	}

	/**
	 * Update by PK.
	 */
	public void updateSchool(DOSchool school) {
		dbe.execute(SQL_UPDATE_SCHOOL, school.getCode(), school.getName(), school.getSchoolType(), school.getProvinceCode(),
				school.getCityCode(), school.getStrictCode(), school.getStreet(), school.getWebsiteUrl(), school.getDescription(),
				school.getEduStage(), school.getIsFrontShow(), school.getVideoFileUid(), school.getIsValid(), 
				school.getLastUpdByUid(), school.getLastUpdDate(), school.getUid());
	}

	/**
	 * Update is valid flag by PK.
	 */
	public void updateIsValid(String isValid, String uid) {
		dbe.execute(SQL_UPDATE_SCHOOL_IS_VALID, isValid, uid);
	}

	/**
	 * Delete row by PK.
	 */
	public void deleteSchool(String uid) {
		dbe.execute(SQL_DEL_SCHOOL, uid);
	}

	public void loadSchoolsByPage(DataPageUtil<School> dataPageUtil) {
		String countSql = "select count(*) from sys_school where 1=1";
		String sql = " select * from sys_school where 1=1";
		SQLWithParams swp = initSQLWithParams(dataPageUtil, null, countSql, sql);
		dataPageUtil.setResult(dbe.list(swp.getSql(), School.class, swp.getParams()));
	}

}